﻿using System;
using System.Data;
using System.Text;
using System.Data.OracleClient;
using DBUtility;//请先添加引用
namespace FiberRMS.DAL.AlarmManager
{
	/// <summary>
	/// 数据访问类ALARM_FILTER。
	/// </summary>
	public class ALARM_FILTER
	{
		public ALARM_FILTER()
		{}
		#region  成员方法

        public DataTable GetAlarmMap(string strWhere, string userid, bool isAdd)
        {
            string strSqlAdd = "SELECT alarmmapid, resourcename, alarmcause, alarmlevel, alarmtype, vendor ";
            strSqlAdd += "FROM alarm_map t ";
            strSqlAdd += "WHERE t.alarmcause IS NOT NULL AND t.alarmlevel IS NOT NULL AND t.alarmtype IS NOT NULL ";
            object obj = OracleHelper.GetSingle("SELECT af.alarmmapid FROM alarm_filter af,alarm_filtermap uaf WHERE af.alarmfilterid = uaf.filterid AND uaf.userid = '" + userid + "' ");
            if (obj != null)
            {
                string[] mapids = obj.ToString().Split(new char[] { ',' });
                StringBuilder sb = new StringBuilder();
                for (int i = 0; i < mapids.Length; i++)
                {
                    sb.Append(" AND alarmmapid <> '" + mapids[i] + "'");
                }
                strSqlAdd += sb.ToString();
            }

            string strSqlUpdate = "SELECT alarmmapid, resourcename, alarmcause, alarmlevel, alarmtype, vendor ";
            strSqlUpdate += "FROM alarm_map t ";
            strSqlUpdate += "WHERE t.alarmcause IS NOT NULL AND t.alarmlevel IS NOT NULL AND t.alarmtype IS NOT NULL ";
            strSqlUpdate += " AND alarmmapid NOT IN(SELECT af.alarmmapid FROM alarm_filter af,alarm_filtermap uaf WHERE af.alarmfilterid = uaf.filterid AND uaf.userid = '" + userid + "')";
            strSqlUpdate += " union ";
            string strSqlUpdate1 = "SELECT alarmmapid, resourcename, alarmcause, alarmlevel, alarmtype, vendor ";
            strSqlUpdate1 += "FROM alarm_map ";
            strSqlUpdate1 += "WHERE alarmcause IS NOT NULL AND alarmlevel IS NOT NULL AND alarmtype IS NOT NULL ";

            DataTable dt = null;
            if (isAdd)
            {
                try
                {
                    dt = OracleHelper.Query(strSqlAdd).Tables[0];
                }
                catch (OracleException oe)
                {
                    XCLib.Log.CLogSingleton.PolicyOneLog(oe.Message, "获取告警映射发生异常", strSqlAdd);
                }
            }
            else
            {
                try
                {
                    if (strWhere != "")
                    {
                        strSqlUpdate1 += strWhere;
                    }
                    dt = OracleHelper.Query(strSqlUpdate + strSqlUpdate1).Tables[0];
                }
                catch (OracleException oe)
                {
                    XCLib.Log.CLogSingleton.PolicyOneLog(oe.Message, "获取告警映射发生异常", strSqlUpdate + strSqlUpdate1);
                }
            }



            return dt;

        }
        /// <summary>
        /// 获取alarmfilter、alarmmap联查结果
        /// </summary>
        /// <returns></returns>        
        public DataTable GetFilters(string userid)
        {
            //string strSql = "select af.ALARMFILTERID,af.FILTERNAME,af.CREATETIME,AM.RESOURCENAME,AM.ALARMTYPE,AM.ALARMLEVEL,AM.ALARMCAUSE,AF.ISUSE ";
            //strSql += " from alarmfilter af,alarmmap am,Userfiltermap ufm ";
            //strSql += " where af.alarmfilterid = ufm.filterid AND ufm.userid = '"+userid+"' ";
            //object obj = OracleHelper.GetSingle("select af.alarmmapid from alarmfilter af,Userfiltermap ufm  where af.alarmfilterid = ufm.filterid AND ufm.userid = '"+userid+"'");
            //string strMapid = obj.ToString();
            //string[] mapids = strMapid.Split(new char[]{','});
            //StringBuilder sb = new StringBuilder();
            //sb.Append(" and ");
            //for (int i = 0; i < mapids.Length; i++)
            //{
            //    sb.Append("AM.ALARMMAPID = '" + mapids[i] + "'");
            //    if (i < mapids.Length - 1)
            //    {
            //        sb.Append(" or ");
            //    }
            //}
            //strSql += sb.ToString();
            string strSql = "select distinct af.ALARMFILTERID,af.FILTERNAME,af.CREATETIME,af.ISUSE ";
            strSql += " from alarm_filter af,alarm_filtermap ufm ";
         //   strSql += " where af.alarmfilterid = ufm.filterid AND ufm.userid = '" + userid + "'  order by  createtime desc ";
            strSql += " where af.alarmfilterid = ufm.filterid   order by  createtime desc ";
            DataTable dt = null;
            try
            {
                dt = OracleHelper.Query(strSql).Tables[0];
                XCLib.Log.CLogSingleton.Debug(XCLib.Log.CLogSingleton.InfoLog, strSql);
            }
            catch (OracleException oe)
            {
                XCLib.Log.CLogSingleton.PolicyOneLog(oe.Message, "读取过滤器失败，检查alarmfilter、alarmmap表", "获得alarmfilter、alarmmap联查结果抛出OracleException");
            }
            return dt;
        }
        public int GetMaxID()
        {
            return OracleHelper.GetMaxID("ALARMFILTERID", "ALARM_FILTER");
        }
		/// <summary>
		/// 是否存在该记录
		/// </summary>
		public bool Exists(string ALARMFILTERID)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) from ALARM_FILTER");
			strSql.Append(" where ALARMFILTERID=:ALARMFILTERID ");
			OracleParameter[] parameters = {
					new OracleParameter(":ALARMFILTERID", OracleType.VarChar,50)};
			parameters[0].Value = ALARMFILTERID;

			return OracleHelper.Exists(strSql.ToString(),parameters);
		}


		/// <summary>
		/// 增加一条数据
		/// </summary>
		public bool Add(FiberRMS.Model.AlarmManager.ALARM_FILTER model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("insert into ALARM_FILTER(");
			strSql.Append("ALARMFILTERID,FILTERNAME,CREATETIME,ISUSE,ALARMMAPID)");
			strSql.Append(" values (");
			strSql.Append(":ALARMFILTERID,:FILTERNAME,:CREATETIME,:ISUSE,:ALARMMAPID)");
			OracleParameter[] parameters = {
					new OracleParameter(":ALARMFILTERID", OracleType.VarChar,10),
					new OracleParameter(":FILTERNAME", OracleType.VarChar,30),
					new OracleParameter(":CREATETIME", OracleType.DateTime),
					new OracleParameter(":ISUSE", OracleType.VarChar,2),
					new OracleParameter(":ALARMMAPID", OracleType.VarChar,2000)};
			parameters[0].Value = model.ALARMFILTERID;
			parameters[1].Value = model.FILTERNAME;
			parameters[2].Value = model.CREATETIME;
			parameters[3].Value = model.ISUSE;
			parameters[4].Value = model.ALARMMAPID;

			int i = OracleHelper.ExecuteSql(strSql.ToString(),parameters);
            if (i > 0)
            {
                return true;
            }
            return false;
		}
		/// <summary>
		/// 更新一条数据
		/// </summary>
		public bool Update(FiberRMS.Model.AlarmManager.ALARM_FILTER model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("update ALARM_FILTER set ");
			strSql.Append("FILTERNAME=:FILTERNAME,");
            strSql.Append("CREATETIME=:CREATETIME,");
			strSql.Append("ISUSE=:ISUSE,");
			strSql.Append("ALARMMAPID=:ALARMMAPID");
			strSql.Append(" where ALARMFILTERID=:ALARMFILTERID ");
			OracleParameter[] parameters = {
					new OracleParameter(":ALARMFILTERID", OracleType.VarChar,60),
					new OracleParameter(":FILTERNAME", OracleType.VarChar,30),
					new OracleParameter(":CREATETIME", OracleType.DateTime),
					new OracleParameter(":ISUSE", OracleType.VarChar,2),
					new OracleParameter(":ALARMMAPID", OracleType.VarChar,2000)};
			parameters[0].Value = model.ALARMFILTERID;
			parameters[1].Value = model.FILTERNAME;
			parameters[2].Value = model.CREATETIME;
			parameters[3].Value = model.ISUSE;
			parameters[4].Value = model.ALARMMAPID;

			int i = OracleHelper.ExecuteSql(strSql.ToString(),parameters);
            if (i > 0)
            {
                return true;
            }
            return false;
		}

		/// <summary>
		/// 删除一条数据
		/// </summary>
		public void Delete(string ALARMFILTERID)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete ALARM_FILTER ");
			strSql.Append(" where ALARMFILTERID=:ALARMFILTERID ");
			OracleParameter[] parameters = {
					new OracleParameter(":ALARMFILTERID", OracleType.VarChar,50)};
			parameters[0].Value = ALARMFILTERID;

			OracleHelper.ExecuteSql(strSql.ToString(),parameters);
		}


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public FiberRMS.Model.AlarmManager.ALARM_FILTER GetModel(string ALARMFILTERID)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ALARMFILTERID,FILTERNAME,CREATETIME,ISUSE,ALARMMAPID from ALARM_FILTER ");
			strSql.Append(" where ALARMFILTERID=:ALARMFILTERID ");
			OracleParameter[] parameters = {
					new OracleParameter(":ALARMFILTERID", OracleType.VarChar,50)};
			parameters[0].Value = ALARMFILTERID;

			FiberRMS.Model.AlarmManager.ALARM_FILTER model=new FiberRMS.Model.AlarmManager.ALARM_FILTER();
			DataSet ds=OracleHelper.Query(strSql.ToString(),parameters);
			if(ds.Tables[0].Rows.Count>0)
			{
				model.ALARMFILTERID=ds.Tables[0].Rows[0]["ALARMFILTERID"].ToString();
				model.FILTERNAME=ds.Tables[0].Rows[0]["FILTERNAME"].ToString();
				if(ds.Tables[0].Rows[0]["CREATETIME"].ToString()!="")
				{
					model.CREATETIME=DateTime.Parse(ds.Tables[0].Rows[0]["CREATETIME"].ToString());
				}
				model.ISUSE=ds.Tables[0].Rows[0]["ISUSE"].ToString();
				model.ALARMMAPID=ds.Tables[0].Rows[0]["ALARMMAPID"].ToString();
				return model;
			}
			else
			{
				return null;
			}
		}

		/// <summary>
		/// 获得数据列表
		/// </summary>
		public DataSet GetList(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ALARMFILTERID,FILTERNAME,CREATETIME,ISUSE,ALARMMAPID ");
			strSql.Append(" FROM ALARM_FILTER ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return OracleHelper.Query(strSql.ToString());
		}

		/*
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetList(int PageSize,int PageIndex,string strWhere)
		{
			OracleParameter[] parameters = {
					new OracleParameter(":tblName", OracleType.VarChar, 255),
					new OracleParameter(":fldName", OracleType.VarChar, 255),
					new OracleParameter(":PageSize", OracleType.Number),
					new OracleParameter(":PageIndex", OracleType.Number),
					new OracleParameter(":IsReCount", OracleType.Clob),
					new OracleParameter(":OrderType", OracleType.Clob),
					new OracleParameter(":strWhere", OracleType.VarChar,1000),
					};
			parameters[0].Value = "ALARM_FILTER";
			parameters[1].Value = "ID";
			parameters[2].Value = PageSize;
			parameters[3].Value = PageIndex;
			parameters[4].Value = 0;
			parameters[5].Value = 0;
			parameters[6].Value = strWhere;	
			return OracleHelper.RunProcedure("UP_GetRecordByPage",parameters,"ds");
		}*/

		#endregion  成员方法
	}
}






